Re: Autoincrement

Herouth Maoz wrote:

> (redirected to the SQL list because it really has nothing to do with
> interfaces):
>
> At 14:07 +0300 on 17/7/98, Federico Passaro wrote:
>
> >   You are right, but it's better to put the autoincrementing field as
> >   the last one like in:
> >
> > CREATE TABLE cliente (
> >         name    varchar(100) UNIQUE NOT NULL,
> >         username        varchar(8) NOT NULL ,
> >         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
> >         );
> >
> > This way you can use the sintax
> >
> > insert into cliente values ('JACK', 'postgres');
> >
> > in place of
> >
> > insert into cliente (name, username) values ('JACK', 'postgres');
>
> No! The syntax may look attractive to you, because you have to write less,
> but you will pay for it in performance! I definitely would not put that
> field last. This is because in the current version of Postgres, any fields
> following the first VARCHAR have a performance penalty. So, in order to
> avoid this, you should place all fixed-sized fields at the beginning, and
> then all the variable-length fields.

When I have to pay this penalty: on insert / update / query ?

>
>
> In any case it is always recommeded to explicitly specify the names of all
> the fields in an insert operation, rather than rely on your memory of the
> correct order.

>
>
> > A more robust solution is to use a trigger. Look at the files
> > <PostGreSQL source dir>/contrib/spi/autoinc.*
>
> I am not sure a solution which depends on writing code in C and having
> postgres superuser privileges can be considered "more robust". I'd
> recommend the use of sequences in any case.

Could you explain your assertion please: the C code I mentioned is ready and
well tested ....

>
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma

federico